"""
Case Type   : 拷贝数据
Case Name   : 使用copy from导入数据指定timestamp format中包含FF6
Create At   : 2024-07-25
Owner       : songjing20
Description :
    1.构造数据文件
    2.创建表
    3.导入数据指定timestamp format中包含FF6并查询
    4.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class CopyFile(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.node_pri = Node('PrimaryDbUser')
        self.constant = Constant()
        self.tb_name = 't_copy_0140'
        self.file_abspath = os.path.join(
            macro.DB_BACKUP_PATH, f"{self.tb_name}.csv")

    def test_copy_file(self):
        text = '-----step1:构造数据文件;expect:成功-----'
        self.log.info(text)
        data = \
'''1,测试数据,2022-08-04-09.27.34.932528,2022-08-04-09.27.34.932528
2,测试数据,2022-08-04-09.27.34.936061,2022-08-04-09.27.34.936061
3,测试数据,2022-08-04-09.27.34.942181,2022-08-04-09.27.34.942181
4,测试数据,2022-08-04-09.27.34.944161,2022-08-04-09.27.34.944161
5,测试数据,2022-08-04-09.27.34.946140,2022-08-04-09.27.34.946140'''
        cmd = f'echo "{data}" > {self.file_abspath};cat {self.file_abspath};'
        self.log.info(cmd)
        cmd_res = self.node_pri.sh(cmd).result()
        self.log.info(cmd_res)
        self.assertIn(data, cmd_res, f'执行失败:{text}')

        text = '-----step2:创建表;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name}(
        id int, name character varying(768) NOT NULL, 
        c_time timestamp without time zone,
        u_time timestamp without time zone
        ) WITH (orientation=row, compression=no);'''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_res,
                      f'执行失败:{text}')

        text = '-----step3:导入数据指定timestamp format中包含FF6并查询;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"copy {self.tb_name} from '{self.file_abspath}' "
            f"with(format csv,timestamp_format 'YYYY-MM-DD-HH24.MI.SS.FF6');"
            f"select * from {self.tb_name} order by 1;")
        self.log.info(res)
        self.assertIn('COPY 5', res, f'执行失败:{text}')
        self.assertIn('1 | 测试数据 | 2022-08-04 09:27:34.932528 | '
                      '2022-08-04 09:27:34.932528\n  2 | 测试数据 | '
                      '2022-08-04 09:27:34.936061 | 2022-08-04 09:27:34.936061'
                      '\n  3 | 测试数据 | 2022-08-04 09:27:34.942181 | '
                      '2022-08-04 09:27:34.942181\n  4 | 测试数据 | '
                      '2022-08-04 09:27:34.944161 | 2022-08-04 09:27:34.944161'
                      '\n  5 | 测试数据 | 2022-08-04 09:27:34.94614  | '
                      '2022-08-04 09:27:34.94614\n(5 rows)',
                      res, f'执行失败:{text}')

    def tearDown(self):
        text = '-----step4:清理环境;expect:成功-----'
        self.log.info(text)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.tb_name} cascade;")
        self.log.info(drop_res)
        cmd = f'rm -f {self.file_abspath};echo $?;'
        self.log.info(cmd)
        cmd_res = self.node_pri.sh(cmd).result()
        self.log.info(cmd_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_res,
                      f'删表失败:{text}')
        self.assertEqual('0', cmd_res, f'执行失败:{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
